/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accessor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import model.Model;

/**
 *
 * @author QUYET
 */
public class CustomerAccessor extends Model {

    public CustomerAccessor() {
    }

    public int addCustomer(Customer customer) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Insert into tblCustomer(email,password,creditPoint,dateCreated,lastLoginDate) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, customer.getEmail());
            ps.setString(2, customer.getPassword());
            ps.setInt(3, 0);
            Date date = new Date();
            SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy H:m:s");
            String timeNow = dateFormat.format(date);
            ps.setString(4, timeNow);
            ps.setString(5, timeNow);
            ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return getInsertID();
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    protected int getInsertID() {
        int id = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("Select TOP 1 * from tblCustomer order by customerID DESC");
            rs = ps.executeQuery();
            if (rs.next()) {
                id = rs.getInt("customerID");
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return id;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }

    public int addCustomerProfile(Customer customer) {
        int rowAffected = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Insert into tblCustomerProfile(customerID,firstName,lastName,company,address,phone) values(?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, customer.getCustomerID());
            ps.setString(2, customer.getFirstName());
            ps.setString(3, customer.getLastName());
            ps.setString(4, customer.getCompany());
            ps.setString(5, customer.getAddress());
            ps.setString(6, customer.getPhone());
            rowAffected = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return rowAffected;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public boolean isEmailExist(String email) {
        boolean isExist = false;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("Select * from tblCustomer Where email = ?");
            ps.setString(1, email);
            rs = ps.executeQuery();
            if (rs.next()) {
                isExist = true;
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return isExist;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public Customer getLoginCustomer(String email, String password) {
        Customer customer = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblCustomer.customerID,tblCustomer.email,tblCustomer.password,tblCustomer.creditPoint,tblCustomerProfile.firstName,";
            sql += "tblCustomerProfile.lastName,tblCustomerProfile.company,tblCustomerProfile.address, tblCustomerProfile.phone ";
            sql += " FROM tblCustomer INNER JOIN tblCustomerProfile ON tblCustomer.customerID = tblCustomerProfile.customerID ";
            sql += " Where tblCustomer.email = ? AND tblCustomer.password = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, email);
            ps.setString(2, password);
            rs = ps.executeQuery();
            if (rs.next()) {;
                customer = new Customer();
                customer.setCustomerID(rs.getInt("customerID"));
                customer.setEmail(rs.getString("email"));
                customer.setPassword(rs.getString("password"));
                customer.setCreaditPoint(rs.getInt("creditPoint"));
                customer.setFirstName(rs.getString("firstName"));
                customer.setLastName(rs.getString("lastName"));
                customer.setCompany(rs.getString("company"));
                customer.setAddress(rs.getString("address"));
                customer.setPhone(rs.getString("phone"));
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return customer;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public int updateCustomerProdile(Customer customer) {
        int affectedRow = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Update tblCustomerProfile set firstName=?,lastName=?,company=?,address=?,phone=? Where customerID = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, customer.getFirstName());
            ps.setString(2, customer.getLastName());
            ps.setString(3, customer.getCompany());
            ps.setString(4, customer.getAddress());
            ps.setString(5, customer.getPhone());
            ps.setInt(6, customer.getCustomerID());
            affectedRow = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return affectedRow;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }

    }
    
    public List<Customer> getAllCustomer() {
        List<Customer> retVal = new ArrayList<Customer>();
        Customer customer = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblCustomer.customerID,tblCustomer.email,tblCustomer.password,tblCustomer.creditPoint,tblCustomerProfile.firstName,";
            sql += "tblCustomerProfile.lastName,tblCustomerProfile.company,tblCustomerProfile.address, tblCustomerProfile.phone ";
            sql += " FROM tblCustomer INNER JOIN tblCustomerProfile ON tblCustomer.customerID = tblCustomerProfile.customerID ";

            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {;
                customer = new Customer();
                customer.setCustomerID(rs.getInt("customerID"));
                customer.setEmail(rs.getString("email"));
                customer.setPassword(rs.getString("password"));
                customer.setCreaditPoint(rs.getInt("creditPoint"));
                customer.setFirstName(rs.getString("firstName"));
                customer.setLastName(rs.getString("lastName"));
                customer.setCompany(rs.getString("company"));
                customer.setAddress(rs.getString("address"));
                customer.setPhone(rs.getString("phone"));
                
                retVal.add(customer);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return retVal;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
    public Customer getCustomerByID(int custID) {
        Customer customer = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT tblCustomer.customerID,tblCustomer.email,tblCustomer.password,tblCustomer.creditPoint,tblCustomerProfile.firstName,";
            sql += "tblCustomerProfile.lastName,tblCustomerProfile.company,tblCustomerProfile.address, tblCustomerProfile.phone ";
            sql += " FROM tblCustomer INNER JOIN tblCustomerProfile ON tblCustomer.customerID = tblCustomerProfile.customerID ";
            sql += " Where tblCustomer.customerID = ? ";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, custID);
            rs = ps.executeQuery();
            while(rs.next()) {;
                customer = new Customer();
                customer.setCustomerID(rs.getInt("customerID"));
                customer.setEmail(rs.getString("email"));
                customer.setPassword(rs.getString("password"));
                customer.setCreaditPoint(rs.getInt("creditPoint"));
                customer.setFirstName(rs.getString("firstName"));
                customer.setLastName(rs.getString("lastName"));
                customer.setCompany(rs.getString("company"));
                customer.setAddress(rs.getString("address"));
                customer.setPhone(rs.getString("phone"));
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return customer;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
    public Double getCustomerPaidHistory(int custID) {
        Double paid = 0.0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT SUM(tblOrderDetails.totalPrice) as 'totalPaid' FROM tblOrders INNER JOIN "
                    + "tblOrderDetails ON tblOrders.orderID = tblOrderDetails.orderID "
                    + "Where tblOrders.customerID = ? AND tblOrders.orderStatus =3";
 
            ps = conn.prepareStatement(sql);
            ps.setInt(1, custID);
            rs = ps.executeQuery();
            if(rs.next()) {
                paid = rs.getDouble("totalPaid");
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return paid;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0.0;
        }
    }
    
    public int deleteCustomer(int custID){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Delete tblCustomer Where customerID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, custID);
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    public int deleteCustomerProfile(int custID){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Delete tblCustomerProfile Where customerID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, custID);
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
}
